var express = require('express')
var router = express.Router()
// middleware that is specific to this router
router.use(function timeLog(req, res, next) {
    console.log('Time: ', Date.now())
    //模块路由中检测是否携带了 token
    if (!req.auth) {
        return res.send({
            status: 403,
            message: "缺少token"
        });
    }
    // checkToken(req, res, next)  --待研究
    next()
})
//  从db文件中导入的数据库模块
var db = require("../db/mysql")
// 库存 列表 
router.get('/list', (req, res) => {
    console.log(req.auth)  // 获取token中信息:{ uid: 9, account: '15527098790', iat: 1721979755, exp: 1722238955 }
    let uid = req.auth.uid;
    // 获取url中的参数 ?page=1&page_size=3,参数放在req.query中 { page: '1', page_size: '3' }
    let { page, page_size } = req.query;
    // 查询limit
    let start = (page - 1) * page_size;
    let limit = ` limit ${start}, ${page_size}`; // limit 0,3
    // 查询库存列表
    let selectSQL = `
    SELECT a.id, a.oldnumber, a.addnumber, a.newnumber, a.usenumber, a.type, b.productname 
    FROM inventory as a 
    LEFT JOIN product_name as b 
    ON a.type = b.id ${limit}`;
    db.query(selectSQL, (error, results) => {
        if (error) console.log(error);
        console.log(results);
        let arr = [];
        results.forEach(item => {
            arr.push({
                id: item.id,
                oldnumber: item.oldnumber,
                addnumber: item.addnumber,
                newnumber: item.newnumber,
                usenumber: item.usenumber,
                type: item.type,
                product_name: item.productname
            });
        });
        // 计算总条数和每种类型的总数
        let totalSQL = `
        SELECT 
            SUM(CASE WHEN type = 1 THEN oldnumber END) AS totalOldNumber_deng,
            SUM(CASE WHEN type = 1 THEN addnumber END) AS totalAddNumber_deng,
            SUM(CASE WHEN type = 1 THEN usenumber END) AS totalUseNumber_deng,
            SUM(CASE WHEN type = 2 THEN oldnumber END) AS totalOldNumber_shui,
            SUM(CASE WHEN type = 2 THEN addnumber END) AS totalAddNumber_shui,
            SUM(CASE WHEN type = 2 THEN usenumber END) AS totalUseNumber_shui,
            COUNT(id) AS totalNum
        FROM inventory`;
        db.query(totalSQL, (err, totalData) => {
            console.log("total:", totalData);
            let totalNum = totalData[0]?.totalNum;
            let total_deng = totalData[0]?.totalOldNumber_deng + totalData[0]?.totalAddNumber_deng - totalData[0]?.totalUseNumber_deng;
            let total_shui = totalData[0]?.totalOldNumber_shui + totalData[0]?.totalAddNumber_shui - totalData[0]?.totalUseNumber_shui;

            res.json({
                code: 0,
                msg: "信息详情",
                data: { list: arr, totalNum, total_deng, total_shui }
            });
        });
    });
});
// 库存--名称 列表--分页
router.get('/product_list', (req, res) => {
    console.log(req.auth);  // 获取token中信息:{ uid: 9, account: '15527098790', iat: 1721979755, exp: 1722238955 }
    let uid = req.auth.uid;
    // 获取url中的参数 ?page=1&page_size=3,参数放在req.query中 { page: '1', page_size: '3' }
    let { page, page_size } = req.query;
    // 查询limit
    let start = (page - 1) * page_size;
    let limit = ` limit ${start}, ${page_size}`; // limit 0,3

    // 查询库存列表
    let selectSQL = `SELECT id, productname FROM product_name ${limit}`;
    db.query(selectSQL, (error, results) => {
        if (error) {
            console.log(error);
            return res.status(500).json({ code: 1, msg: '数据库查询错误', data: {} });
        }
        console.log(results);
        let arr = [];
        results.forEach(item => {
            arr.push({
                id: item.id,
                productname: item.productname,
            });
        });

        // 查询总条数
        let countSQL = "SELECT COUNT(*) as total FROM product_name";
        db.query(countSQL, (err, countResults) => {
            if (err) {
                console.log(err);
                return res.status(500).json({ code: 1, msg: '数据库查询错误', data: {} });
            }
            let totalNum = countResults[0].total;

            res.json({
                code: 0,
                msg: "信息详情",
                data: { arr, totalNum }  // 在响应数据中加入总数
            });
        });
    });
});
// 库存--名称 列表--全部（all）
router.get('/product_list_all', (req, res) => {
    // 查询库存列表
    let selectSQL = `SELECT id, productname FROM product_name`;
    db.query(selectSQL, (error, results) => {
        if (error) {
            console.log(error);
            return res.status(500).json({ code: 1, msg: '数据库查询错误', data: {} });
        }
        console.log(results);
        let arr = [];
        results.forEach(item => {
            arr.push({
                id: item.id,
                productname: item.productname,
            });
        });
        res.json({
            code: 0,
            msg: "信息详情",
            data: arr   // 在响应数据中加入总数
        });
    });
});
// 库存添加 修改 商品
router.post('/change', (req, res) => {
    let { type, oldnumber, addnumber, subtime } = req.body
    let newnumber = Number(oldnumber) + Number(addnumber)
    db.query('INSERT INTO inventory (type,oldnumber,addnumber,newnumber,subtime) VALUES (?,?,?,?,?)',
        [type, oldnumber, addnumber, newnumber, subtime],
        function (error, results) {
            if (error) {
                res.json({ msg: "审核库存添加错误", code: 1, data: [] })
            } else {
                res.json({ msg: '审核库存添加成功', code: 0, data: { type, oldnumber, addnumber, newnumber, subtime } })
            }
        })
    // res.json({ msg: "审核库存添加修改商品列表", code: 1, data: [] })
})
// 库存--名称 添加商品
router.post('/product_add', (req, res) => {
    let { addname } = req.body
    db.query('INSERT INTO product_name (productname) VALUES (?)',
        [addname],
        function (error, results) {
            if (error) {
                res.json({ msg: "添加失败", code: 1, data: [] })
            } else {
                res.json({ msg: '添加成功', code: 0, data: { addname } })
            }
        })
    // res.json({ msg: "审核库存添加修改商品列表", code: 1, data: [] })
})
// 库存--名称  --删除
router.get('/product_del/:id', (req, res) => {
    let id = parseInt(req.params.id)
    db.query('DELETE FROM product_name WHERE id = ?',
        [id],
        function (error, results) {
            if (error) {
                res.json({ msg: "添加失败", code: 1 })
            } else {
                res.json({ msg: '添加成功', code: 0 })
            }
        })
    // res.json({ msg: "审核库存添加修改商品列表", code: 1, data: [] })
})
// 库存--名称  --删除--多个
router.post('/product_dels', (req, res) => {
    // 从请求体中获取id数组
    let ids = req.body.ids; // 假设前端以这种方式传递数组：{ ids: [1, 2, 3] }

    // 验证ids是否为数组且不为空
    if (!Array.isArray(ids) || ids.length === 0) {
        return res.status(400).json({ msg: '无效的参数', code: 1 });
    }

    // 将ids数组转换为字符串，以适配SQL语句中的IN操作符
    let idsPlaceholder = ids.map(() => '?').join(', ');
    let deleteSQL = `DELETE FROM product_name WHERE id IN (${idsPlaceholder})`;

    // 执行SQL语句
    db.query(deleteSQL, ids, function (error, results) {
        if (error) {
            console.log(error);
            res.status(500).json({ msg: "删除失败", code: 1 });
        } else {
            res.json({ msg: '删除成功', code: 0, data: { deletedCount: results.affectedRows } });
        }
    });
});
// 库存查看 详情
router.get('/details', (req, res) => {
    let search = req.query.search
    // console.log(req.query.search)
    let searchSQL = `SELECT * FROM inventory WHERE type LIKE '%${search}%'`
    db.query(searchSQL,
        function (error, results) {
            if (error) {
                res.send({ msg: '查询错误', code: 1, data: error })
            }
            let addnumber = 0;
            for (let i = 0; i < results.length; i++) {
                console.log(results[i].newnumber)
                addnumber = Number(results[i].newnumber) + Number(addnumber)
            } addnumber
            res.send({ msg: '查询详情', code: 0, data: { number: addnumber } })
        }
    )
})
// 库存搜索--模糊查询
router.get("/search", (req, res) => {
    let { oldnumber, newnumber, time_range, type, page, page_size } = req.query;

    // 查询limit
    let start = (page - 1) * page_size;
    let limit = ` LIMIT ${start}, ${page_size}`; // limit 0,10

    // 根据搜索的内容不同，生成查询条件
    let where = 'WHERE 1 = 1';
    if (oldnumber) {
        where += ` AND a.oldnumber LIKE '%${oldnumber}%'`;
    }

    if (time_range) {
        // 假设time_range是一个以逗号分隔的字符串，如 "开始时间,结束时间"
        let [startTime, endTime] = time_range.split(',');
        where += ` AND a.subtime BETWEEN '${startTime}' AND '${endTime}'`;
    }

    if (newnumber !== 'not' && newnumber !== '') {
        where += ` AND a.newnumber > '${newnumber}'`;
    }

    if (type !== 'not' && type !== '') {
        where += ` AND a.type = ${type}`;
    }

    // SQL查询语句（分页查询）
    let selectSQL = `
    SELECT a.id, a.oldnumber, a.addnumber, a.usenumber, a.newnumber, a.type, b.productname 
    FROM inventory AS a 
    LEFT JOIN product_name AS b ON a.type = b.id 
    ${where} ${limit}`;

    // 计算总数的SQL查询语句
    let countSQL = `
    SELECT COUNT(*) AS total
    FROM inventory AS a
    LEFT JOIN product_name AS b ON a.type = b.id 
    ${where}`;

    // 先执行总数查询
    db.query(countSQL, function (error, countResults) {
        if (error) {
            console.error(error);
            return res.status(500).json({ code: 1, msg: "数据库查询错误", error: error.message });
        }
        let total = countResults[0].total; // 获取总数

        // 再执行分页查询
        db.query(selectSQL, function (error, results) {
            if (error) {
                console.error(error);
                return res.status(500).json({ code: 1, msg: "数据库查询错误", error: error.message });
            }

            let arr = results.map(row => ({
                id: row.id,
                oldnumber: row.oldnumber,
                newnumber: row.newnumber,
                usenumber: row.usenumber,
                addnumber: row.addnumber,
                type: row.type,
                product_name: row.productname
            }));

            // 返回分页数据和总数
            res.json({
                code: 0,
                msg: "搜索详情",
                data: arr,
                total: total
            });
        });
    });
});
// 库存名称搜索--模糊查询
router.get('/search_product', (req, res) => {
    console.log(req.auth);  // 获取token中信息:{ uid: 9, account: '15527098790', iat: 1721979755, exp: 1722238955 }
    let uid = req.auth.uid;
    // 获取url中的参数 ?keyword=xxx, 参数放在req.query中 { keyword: 'xxx' }
    let { keyword, page, page_size } = req.query;

    // 验证搜索关键词是否存在
    if (!keyword) {
        return res.status(400).json({ code: 1, msg: '缺少搜索关键词', data: {} });
    }

    // 查询limit
    let start = (page - 1) * page_size;
    let limit = ` limit ${start}, ${page_size}`;

    // 构造搜索SQL语句，使用参数化查询防止SQL注入
    let searchSQL = `
    SELECT id, productname 
    FROM product_name 
    WHERE productname LIKE ? 
    ${limit}`;
    db.query(searchSQL, [`%${keyword}%`], (error, results) => { // 使用%作为通配符进行模糊匹配
        if (error) {
            console.log(error);
            return res.status(500).json({ code: 1, msg: '数据库查询错误', data: {} });
        }
        let arr = results.map(item => ({
            id: item.id,
            productname: item.productname,
        }));

        // 查询总条数
        let countSQL = "SELECT COUNT(*) as total FROM product_name WHERE productname LIKE ?";
        db.query(countSQL, [`%${keyword}%`], (err, countResults) => {
            if (err) {
                console.log(err);
                return res.status(500).json({ code: 1, msg: '数据库查询错误', data: {} });
            }
            let totalNum = countResults[0].total;

            res.json({
                code: 0,
                msg: "搜索结果",
                data: { arr, totalNum }  // 在响应数据中加入总数
            });
        });
    });
});
// 库存修改--获取数据
router.get("/edit/:id", (req, res) => {
    let id = parseInt(req.params.id)
    let mysql = `SELECT a.id,a.oldnumber,a.addnumber,a.newnumber,a.usenumber,a.type,b.productname 
    FROM inventory as a 
    left join product_name as b 
    on a.type = b.id
    WHERE a.id=?
    `
    db.query(mysql, [id], function (error, results) {
        if (error) {
            console.log(error)
        } else {
            var id;
            var oldnumber;
            var addnumber;
            var newnumber;
            var usenumber;
            var type;
            var productname;
            var arr = [];
            for (let i = 0; i < results.length; i++) {
                id = results[i].id
                oldnumber = results[i].oldnumber
                addnumber = results[i].addnumber
                newnumber = results[i].newnumber
                usenumber = results[i].usenumber
                productname = results[i].productname
                type = results[i].type
                arr.push({ id, oldnumber, addnumber, newnumber, usenumber, type, productname })
            }
            // 响应数据 ,扁平化数组 Array.flat(1)
            res.send({ code: 0, msg: '查询数据', data: arr });

        }
    })
})
// 库存修改--保存信息
router.post('/save_edit/:id', (req, res) => {
    // 声明body
    let id = req.params.id; //动态路由中的参数通过params取
    let { oldnumber, addnumber, newnumber, usenumber } = req.body; //普通提交的数据，在req.body中取到
    // 执行sql语句
    db.query('UPDATE inventory SET oldnumber=?,addnumber=?,newnumber=?,usenumber=? WHERE id=?',
        [oldnumber, addnumber, newnumber, usenumber, id], function (error, results) {
            // 响应信息
            if (error) {
                console.log(error)
            } else {
                res.json({ msg: "修改完毕", code: 0 })
            }
        })
    // res.json({ msg: "人员修改", code: 1, data: [] })
})
// 获取产品名称数据
router.get("/product_name/:id", (req, res) => {
    let id = parseInt(req.params.id);
    let mysql = `
    SELECT id, productname 
    FROM product_name
    WHERE id=?
    `;
    db.query(mysql, [id], function (error, results) {
        if (error) {
            console.log(error);
            res.status(500).json({ code: 1, msg: '数据库查询错误', data: [] });
        } else {
            let arr = results.map(item => ({
                id: item.id,
                productname: item.productname
            }));
            // 响应数据
            res.json({ code: 0, msg: '查询数据', data: arr });
        }
    });
});
// 保存产品名称数据
router.post("/save_product_name/:id", (req, res) => {
    let id = req.params.id; // 动态路由中的参数通过params取
    let { productname } = req.body; // 普通提交的数据，在req.body中取到
    // 执行sql语句
    db.query('UPDATE product_name SET productname=? WHERE id=?',
        [productname, id], function (error, results) {
            // 响应信息
            if (error) {
                console.log(error);
                res.status(500).json({ msg: "修改失败", code: 1 });
            } else {
                res.json({ msg: "修改完毕", code: 0 });
            }
        });
});

// 库存历史 记录 
router.get('/record', (req, res) => {
    res.json({ msg: "库存历史记录", code: 1, data: [] })
})
module.exports = router;